Download the Sql File attached to the
Run Script that is attached to create your Dictionary tables in the ODS Database. Once ran the tables will be made up of these columns.
After creating the Dictionary tables in ODS Database- use SSIS to populate the Dictionary tables using the OLDB_Source as source. To load the clean data into the Dictionary tables - used Derived Column Transformation and Data Conversion Transformation to load the Staging Files into OLDB_Destination ie Dictionary tables
Repeat the same steps to load all the dictionary tables.
DTF -
Dictionary Incident Details
Derived Column
Data Conversion
Lookup
DFT - -
Dictionary Migration -the code used as source table is provided below .
select State,
code,
case when
[dbo].[USStatesAndCode_Stg].State in (
'Alabama', 'Connecticut', 'Delaware',
'Florida', 'Georgia', 'Kentucky', 'Louisiana',
'Maine', 'Maryland',
'Massachusetts', 'Michigan', 'Mississippi',
'New Hampshire', 'New Jersey', 'New York', 'North
Carolina', 'Ohio', 'Pennsylvania',
'Rhode Island', 'South Carolina', 'Tennessee',
'Vermont', 'Virginia', 'West Virginia' ) then 'YES'
end AtlanticFlyway,
case when [dbo].[USStatesAndCode_Stg].State in (
'Alaska', 'Colorado', 'Kansas', 'Montana',
'Nebraska', 'New Mexico', 'North Dakota', 'Oklahoma',
'South Dakota', 'Texas',
'Utah', 'Wyoming' ) then 'YES' end CentralFlyway,
case when [dbo].[USStatesAndCode_Stg].State in (
'Alaska', 'Arizona', 'California', 'Colorado',
'Idaho', 'New Mexico', 'Nevada', 'Montana', 'Oregon',
'Utah',
'Washington', 'Wyoming' ) then 'YES' end
PacificFlyway,
case when [dbo].[USStatesAndCode_Stg].State in (
'Alabama', 'Alaska', 'Arkansas', 'Indiana',
'Illinois', 'Iowa', 'Kansas', 'Kentucky', 'Louisiana',
'Ohio', 'Oklahoma',
'Michigan', 'Missouri', 'Mississippi', 'Nebraska', 'North
Dakota', 'South Dakota', 'Tennessee', 'Texas', 'Wisconsin'
) then 'YES' else null end MississippiFlyway
from [dbo].[USStatesAndCode_Stg]
Derived Column
Data Conversion
Lookup
DTF -
Dictionary Airport Details
Derived Column
Data Conversion
Lookup
DTF-
Dictionary Airlines Details
Derived Column
Data Conversion
Lookup
DTF -
Dictionary Make&Model Details
Derived Column
Data Conversion
Lookup
DFT-
Dictionary PilotsWarned Details
Derived Column
Data Conversion
Lookup
DTF
-Dictionary Aircraft Details
Derived Column
Num_Engs
ISNULL(NUMENGS) || NUMENGS == "" ? NULL(DT_I4) :((DT_I4)NUMENGS)
TYPEENG ==
"A" ? "Reciprocating Engine" : TYPEENG == "B" ?
"Turbojet" : TYPEENG == "C" ? "Turboprop" :
TYPEENG == "D" ? "Turbofan" : TYPEENG == "E" ?
"Glider" : TYPEENG == "F" ? "Turboshaft" :
"Other"
Case statement used to populate the Airplane_MASS Column -
ACMASS ==
"1" ? "2,250 kg or less" : ACMASS == "2" ?
"2251-5700 kg" : ACMASS == "3" ? "5,701-27,000
kg" : ACMASS == "4" ? "27,001-272,000 kg" :
"above 272,000 kg"
Case statement used to populate
the Airplane_Types Column -
ACCLASS ==
"A" ? "Airplane" : ACCLASS == "B" ?
"Helicopter" : ACCLASS == "C" ? "Glider" :
ACCLASS == "D" ? "Balloon" : ACCLASS == "F" ?
"Dirigible" : ACCLASS == "I" ? "Gyroplane" :
ACCLASS == "J" ? "Ultralight" : "Other"
Data Conversion
Lookup
DTF - Dictionary Flight Details
Derived Column
Code
for SpeedOfFlight & HeightOfFlight
ISNULL(SPEED) || SPEED == "" ? NULL(DT_I4) : ((DT_I4)SPEED)
ISNULL(HEIGHT) || HEIGHT == "" ? NULL(DT_I4) : ((DT_I4)HEIGHT)
Data Conversion
Lookup
DFT -
Dictionary Brid Details
Derived Column
Data Conversion
Lookup
DFT- Dictionary Damages Details
Derived Column
Case statement used to populate the OutOfService Column –
ISNULL(AOS) || AOS == "" ? NULL(DT_I4) : ((DT_I4)AOS)
Case statement used to
populate the DamageDescription Column -
DAMAGELEVEL
== "N" ? "None" : DAMAGELEVEL == "M" ?
"Minor" : DAMAGELEVEL == "M?" ? "Uncertain Level"
: DAMAGELEVEL == "S" ? "Substantial" : DAMAGELEVEL ==
"D" ? "Destroyed" : DAMAGELEVEL == "Class A" ?
"Damage Over $2,000,000 (Military)" : DAMAGELEVEL == "Class
B" ? "Damage Between $500,000 and $2,000,000 (Military)" :
DAMAGELEVEL == "Class C" ? "Damage between $50,000 amd $499,999
(Military)" : DAMAGELEVEL == "Class D" ? "Damage between
$20,000 and $49,999 (Military)" : DAMAGELEVEL == "Class E" ? "Damage
less than $20,000 (Military)" : DAMAGELEVEL
Case statement used to populate
the EFFECT Column -
EFFECT ==
"Aborted Take-off, Other" ? REPLACE(EFFECT,"Aborted Take-off,
Other","Aborted Take-off") : EFFECT == "None, Precautionary
Landing" ? REPLACE(EFFECT,"None, Precautionary
Landing","Precautionary Landing") : EFFECT == "Other,
Precautionary Landing" ? REPLACE(EFFECT,"Other, Precautionary
Landing","Precautionary Landing") : EFFECT == "None,
Other" ? REPLACE(EFFECT,"None, Other","Other") :
EFFECT
Data Conversion
Lookup
DFT- Seasons Details Dictionary
Derived Column
Case statement used to populate the MonthNameColumn -
INCIDENTMONTH == 1 ? "January" :
INCIDENTMONTH == 2 ? "February" : INCIDENTMONTH == 3 ?
"March" : INCIDENTMONTH == 4 ? "April" : INCIDENTMONTH == 5
? "May" : INCIDENTMONTH == 6 ? "June" : INCIDENTMONTH == 7
? "July" : INCIDENTMONTH == 8 ? "August" : INCIDENTMONTH ==
9 ? "September" : INCIDENTMONTH == 10 ? "October" :
INCIDENTMONTH == 11 ? "November" : INCIDENTMONTH == 12 ?
"December" : "Invalid Month"
Case statement used to populate
the Seasons Column - MonthName
(INCIDENTMONTH == 12 || INCIDENTMONTH == 1 ||
INCIDENTMONTH == 2) ? "Winter" : (INCIDENTMONTH == 3 || INCIDENTMONTH
== 4 || INCIDENTMONTH == 5) ? "Spring" : (INCIDENTMONTH == 6 ||
INCIDENTMONTH == 7 || INCIDENTMONTH == 8) ? "Summer" : (INCIDENTMONTH
== 9 || INCIDENTMONTH == 10 || INCIDENTMONTH == 11) ? "Fall" :
"Invalid Season"
Lookup
DFT - Tbl_BirdStudy
Derived Column
Code for DamagedIndicator,
CostsRepair, OtherCosts, FatalitiesNR,InjuriesNR
INDICATED_DAMAGE == "TRUE" ? 1 : INDICATED_DAMAGE == "FALSE" ? 0 : 2
ISNULL(COSTREPAIRS) || COSTREPAIRS == "" ? NULL(DT_I4) : ((DT_I4)COSTREPAIRS)
ISNULL(OTHERCOST) || OTHERCOST == "" ? NULL(DT_I4) : ((DT_I4)OTHERCOST)
ISNULL(NRFATALITIES) || NRFATALITIES == "" ? NULL(DT_I4) : ((DT_I4)NRFATALITIES)
ISNULL(NRINJURIES) || NRINJURIES == "" ? NULL(DT_I4) : ((DT_I4)NRINJURIES)
The Dictionary tables is populated with Data.
